# delimit;

* This file creates num variables needed from extracted files;
* It needs crtr_att1 - crtr_attr88 and cma_attr3903 and cma_attr3905;

* Our naming convention is: measure_account_delinquency;


* Special variables;

gen fbankrupt7 = 0;
replace fbankrupt7 = 1 if cust_attr290 ~= "";

gen fbankrupt13 = 0;
replace fbankrupt13 = 1 if cust_attr291 ~= "";

gen fbankrupt2 = (fbankrupt7 | fbankrupt13);

gen fbankrupt = 0 if cma_attr3903 == "0";
replace fbankrupt = 1 if cma_attr3903 == "1";

tab fbankrupt fbankrupt2;

drop cma_attr3903;

gen fforeclosure = 0 if cma_attr3905 == "0";
replace fforeclosure = 1 if cma_attr3905 == "1";
drop cma_attr3905;

gen dead = 0;
replace dead = 1 if cust_attr292 == "Y";

drop cust_attr290 cust_attr291 cust_attr292;

* Create number of accounts variables;
rename crtr_attr1 num_all_tot;
gen num_all_90 = crtr_attr15 + crtr_attr16 + crtr_attr17 + crtr_attr18;

rename crtr_attr2 num_autof_tot;
gen num_autof_90 = crtr_attr22 + crtr_attr23 + crtr_attr24 + crtr_attr25; 

rename crtr_attr3 num_autob_tot;
gen num_autob_90 = crtr_attr29 + crtr_attr30 + crtr_attr31 + crtr_attr32; 

rename crtr_attr4 num_bankc_tot;
gen num_bankc_90 = crtr_attr36 + crtr_attr37 + crtr_attr38 + crtr_attr39; 

rename crtr_attr5 num_consf_tot;
gen num_consf_90 = crtr_attr43 + crtr_attr44 + crtr_attr45 + crtr_attr46; 

rename crtr_attr6 num_fmrtg_tot;
gen num_fmrtg_90 = crtr_attr50 + crtr_attr51 + crtr_attr52 + crtr_attr53; 

rename crtr_attr7 num_helin_tot;
gen num_helin_90 = crtr_attr57 + crtr_attr58 + crtr_attr59 + crtr_attr60; 

rename crtr_attr8 num_helre_tot;
gen num_helre_90 = crtr_attr64 + crtr_attr65 + crtr_attr66 + crtr_attr67; 

rename crtr_attr9 num_retal_tot;
gen num_retal_90 = crtr_attr71 + crtr_attr72 + crtr_attr73 + crtr_attr74; 

*rename crtr_attr10 num_stdln_tot;
*gen num_stdln_90 = crtr_attr78 + crtr_attr79 + crtr_attr80 + crtr_attr81; 

rename crtr_attr11 num_other_tot;
gen num_other_90 = crtr_attr85 + crtr_attr86 + crtr_attr87 + crtr_attr88; 


drop crtr_attr12-crtr_attr88;
compress;
desc;

foreach del in tot 90 {;
  gen num_auto_`del' = num_autof_`del' + num_autob_`del';
  gen num_bcret_`del' = num_bankc_`del' + num_retal_`del';
  gen num_home_`del' = num_fmrtg_`del' + num_helin_`del' + num_helre_`del';
  gen num_hel_`del' = num_helin_`del' + num_helre_`del';
  gen num_exhome_`del' = num_auto_`del' + num_bcret_`del' + num_consf_`del' + num_other_`del';
  gen num_other2_`del' = num_consf_`del' + num_other_`del';
};

* Create credit demand and supply proxies;
gen inq3m = cma_attr3000;
replace inq3m = . if cma_attr3000 >= 90 & cma_attr3000 ~= .;

egen ncid = group(cid);
tsset ncid qdate;

sort ncid qdate;
gen l1inq3m = l1.inq3m;
gen l2inq3m = l2.inq3m;
gen l3inq3m = l3.inq3m;

gen numnewacct = 0 if num_all_tot ~= . & l1.num_all_tot ~= .;
replace numnewacct = num_all_tot - l1.num_all_tot if (num_all_tot > l1.num_all_tot);

drop ncid;

compress;
desc;
